/**
 * 2015年6月4日上午9:18:26
 * DbGarbage.javaDbGarbage
 */
package com.service.db.impl;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.MessageFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.service.bean.company.Company;
import com.service.bean.garbage.GarbageBean;
import com.service.bean.garbage.GarbageDataSync;
import com.service.bean.other.FrameResult;
import com.service.cache.CacheManager;
import com.service.db.DbConnectionManager;
import com.service.db.PooledConnection;
import com.service.other.Logger;
import com.service.tools.Tools;


/**
 * 2015年6月4日上午9:18:26
 *
 * 张益飞
 */
public class DbGarbage {
	/**
	 * 批量保存垃圾称重对象
	 * 
	 */
	public void batchGarbageData(Company company) {
		if (CacheManager.instance().getGarbageQueue(company).size()==0) {
			return;
		}
		PooledConnection con = null;
		Statement pstmt = null;

		try {
			int x = 0;
			boolean isEnd = false;
			con = DbConnectionManager.getConnection(company);
			con.setAutoCommit(false); 
			pstmt = con.prepareStatement();
			String sql = "insert into crc_GpsCollectionSignal (GPSNO,GPSTime,Lng,Lat,EquipmentId,RFID,CollectionAmt,BaiduLng,BaiduLat,rawData,DriverNo) "
					+ "values";
			do{
				GarbageBean bean = CacheManager.instance().getGarbageQueue(company).poll();
				pstmt.addBatch(sql+MessageFormat.format("({0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10})",bean.getAll()));
				isEnd = CacheManager.instance().getGarbageQueue(company).isEmpty();
				x++;
				if(x==1000||isEnd){
					long y = System.currentTimeMillis();
					pstmt.executeBatch();
					con.commit();
					pstmt.clearBatch();
					long z = System.currentTimeMillis();
					Logger.println("[" + company.companyName + "]成功存储"+x+"条垃圾称重数据,用时"+(z-y)+"毫秒.");
					System.out.println("["+company.companyName+"]管道中剩余"+CacheManager.instance().getGarbageQueue(company).size()+"条垃圾称重数据");
					x=0;
				}
			} while (!isEnd);
			con.setAutoCommit(true);
		} catch (SQLException e) {
			Logger.err(e);
		} finally {
			try {
				if(pstmt!=null && !pstmt.isClosed()){
					pstmt.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (SQLException e) {
				Logger.err(e);
			}
		}
	}
	
	/**
	 * 一辆车的垃圾下发查询
	 * @param company
	 * @param typeNo
	 * @param gpsNo
	 * @return
	 */
	public static FrameResult getFrameCountByGpsNo(Company company,String gpsNo,String typeNo){
		PooledConnection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs= null;
		FrameResult bean = new FrameResult();
		try {
			con = DbConnectionManager.getConnection(company);

			pstmt = con.prepareStatement("select COUNT(id) frameCount from crc_CollectionData where typeNo = ? and GPSNO = ?");
			pstmt.setString(1, typeNo);
			pstmt.setString(2, gpsNo);
			rs = pstmt.executeQuery();
			while(rs.next()){				
				bean.setDeviceNo(gpsNo);
				bean.setSize(rs.getInt("frameCount"));
			}
		} catch (Exception e) {
			Logger.err(e);
		}finally{
			try{
				if(rs!=null && !rs.isClosed()){
					rs.close();
				}
				if(pstmt!=null && !pstmt.isClosed()){
					pstmt.close();
				}
				if (con != null) {
					con.close();
				}
			}catch(Exception e){
				Logger.err(e);
			}
		}
		return bean;
	}	

	/**
	 * 数据
	 * @param company
	 * @param typeNo
	 * @return
	 */
	public static FrameResult getFrameCount(Company company,String gpsNo,String typeNo){
		PooledConnection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs= null;
		FrameResult bean = new FrameResult();
		try {
			con = DbConnectionManager.getConnection(company);
			pstmt = con.prepareStatement("select COUNT(1) frameCount from crc_CollectionData where gpsno = ? and typeNo = ?");
			pstmt.setString(1, gpsNo);
			pstmt.setString(2, typeNo);
			rs = pstmt.executeQuery();
			while(rs.next()){				
				bean.setDeviceNo(gpsNo);
				bean.setSize(rs.getInt("frameCount"));
			}
		} catch (Exception e) {
			Logger.err(e);
		}finally{
			try{
				if(rs!=null && !rs.isClosed()){
					rs.close();
				}
				if(pstmt!=null && !pstmt.isClosed()){
					pstmt.close();
				}
				if (con != null) {
					con.close();
				}
			}catch(Exception e){
				Logger.err(e);
			}
		}
		return bean;
	}
	
	/**
	 * 5分钟之前的旧数据
	 * @param company
	 * @param typeNo
	 * @return
	 */
	public static FrameResult getFrameCountIsOld(Company company,String gpsNo,String typeNo){
		PooledConnection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs= null;
		FrameResult bean = new FrameResult();
		try {
			con = DbConnectionManager.getConnection(company);
			pstmt = con.prepareStatement("select COUNT(1) frameCount from crc_CollectionData where gpsno = ? and typeNo = ? and DATEDIFF(MI,AddTime,GETDATE())>5");
			pstmt.setString(1, gpsNo);
			pstmt.setString(2, typeNo);
			rs = pstmt.executeQuery();
			while(rs.next()){				
				bean.setDeviceNo(gpsNo);
				bean.setSize(rs.getInt("frameCount"));
			}
		} catch (Exception e) {
			Logger.err(e);
		}finally{
			try{
				if(rs!=null && !rs.isClosed()){
					rs.close();
				}
				if(pstmt!=null && !pstmt.isClosed()){
					pstmt.close();
				}
				if (con != null) {
					con.close();
				}
			}catch(Exception e){
				Logger.err(e);
			}
		}
		return bean;
	}
		
	
	//根据操作对象。设备编号获取垃圾信息
	public static List<GarbageDataSync> getGarbageList(Company company,String deviceNo,String typeNo){
		PooledConnection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs= null;
		List<GarbageDataSync> list = new ArrayList<GarbageDataSync>();
		try {
			con = DbConnectionManager.getConnection(company);
			pstmt = con.prepareStatement("select id,TypeNo,OprStatus,GPSNO,ParaData1,ParaData2,ParaData3,ParaData4,ParaData5 from crc_CollectionData  where TypeNo=? and GPSNO=? order by AddBy asc");
			pstmt.setString(1, typeNo);
			pstmt.setString(2, deviceNo);
			rs = pstmt.executeQuery();
			while(rs.next()){
				GarbageDataSync bean = new GarbageDataSync();
				bean.id = rs.getString("id");
				bean.typeNo = rs.getString("TypeNo");
				bean.OprStatus = rs.getString("OprStatus");
				bean.GPSNO = rs.getString("GPSNO");
				bean.ParaData1 = rs.getString("ParaData1");
				bean.ParaData2 = rs.getString("ParaData2");
				bean.ParaData3 = rs.getString("ParaData3");
				bean.ParaData4 = rs.getString("ParaData4");
				bean.ParaData5 = rs.getString("ParaData5");
				bean.companyCode = company.code;
				list.add(bean);
			}
		} catch (Exception e) {
			Logger.err(e);
		}finally{
			try{
				if(rs!=null && !rs.isClosed()){
					rs.close();
				}
				if(pstmt!=null && !pstmt.isClosed()){
					pstmt.close();
				}
				if (con != null) {
					con.close();
				}
			}catch(Exception e){
				Logger.err(e);
			}
		}
		return list;
	}
	
	
	//根据companyCode取company
	public static Company getCompany(String companyCode){
		PooledConnection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs= null;
		Company company = null;
		try {
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement("select *  from ref_Company where Status='active' and CompanyCode=? ");
			pstmt.setString(1, companyCode);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				company = new Company();
				company.code = companyCode;
				company.place = rs.getString("Place");
				company.companyName = rs.getString("CompanyName");
				company.dataConnection = rs.getString("JavaConnection");
				company.dbUser = rs.getString("UserId");
				company.dbPwd = rs.getString("PassWord");
			}
		} catch (Exception e) {
			Logger.err(e);
		}finally{
			try{
				if(rs!=null && !rs.isClosed()){
					rs.close();
				}
				if(pstmt!=null && !pstmt.isClosed()){
					pstmt.close();
				}
				if (con != null) {
					con.close();
				}
			}catch(Exception e){
				Logger.err(e);
			}
		}
		return company;
	}
	
	//删除更新后的信息
	public static void deleteCollectionDataById(String id,Company company){
		PooledConnection con = null;
		PreparedStatement pstmt = null;
		try {
			con = DbConnectionManager.getConnection(company);
			pstmt = con.prepareStatement("delete from crc_CollectionData where id = ? ");
			pstmt.setString(1, id);
			pstmt.execute();
		} catch (Exception e) {
			Logger.err(e);
		}finally{
			try{
				if(pstmt!=null && !pstmt.isClosed()){
					pstmt.close();
				}
				if (con != null) {
					con.close();
				}
			}catch(Exception e){
				Logger.err(e);
			}
		}
	}
	
	/**
	 * 批量保存垃圾称重报警数据
	 * @param company
	 */
	public void batchGarbageAlarmData(Company company) {
		if (CacheManager.instance().getGarbageAlarmQueue(company).size()==0) {
			return;
		}
		PooledConnection con = null;
		Statement pstmt = null;
		try {
			int x = 0;
			boolean isEnd = false;
			con = DbConnectionManager.getConnection(company);
			con.setAutoCommit(false); 
			pstmt = con.prepareStatement();
			String sql = "insert into crc_EquipmentWeighException (EquipmentId,ExceptionTime) "
					+ "values";
			do{
				GarbageBean bean = CacheManager.instance().getGarbageAlarmQueue(company).poll();
				pstmt.addBatch(sql+MessageFormat.format("({0},{1})",bean.getAlarmAll()));
				isEnd = CacheManager.instance().getGarbageAlarmQueue(company).isEmpty();
				x++;
				if(x==1000||isEnd){
					long y = System.currentTimeMillis();
					pstmt.executeBatch();
					con.commit();
					pstmt.clearBatch();
					long z = System.currentTimeMillis();
					Logger.println("[" + company.companyName + "]成功存储"+x+"条垃圾称重报警数据,用时"+(z-y)+"毫秒.");
					System.out.println("["+company.companyName+"]管道中剩余"+CacheManager.instance().getGarbageAlarmQueue(company).size()+"条垃圾称重报警数据");
					x=0;
				}
			} while (!isEnd);
			con.setAutoCommit(true);
		} catch (SQLException e) {
			Logger.err(e);
		} finally {
			try {
				if(pstmt!=null && !pstmt.isClosed()){
					pstmt.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (SQLException e) {
				Logger.err(e);
			}
		}
	}

}
